SET ANSI_NULLS ON

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPackageLicenseReport2]') AND type IN (N'P', N'PC'))
BEGIN
    DROP PROCEDURE [dbo].[AddPackageLicenseReport2]
END
GO

CREATE PROCEDURE [dbo].[AddPackageLicenseReport2]
(
     @sequence INT,
     @packageId NVARCHAR(128),
     @version NVARCHAR(64),
     @reportUrl NVARCHAR(256) = NULL,
     @licenseNames LicenseNamesList READONLY,
     @comment NVARCHAR(256) = NULL,
     @whatIf BIT = 0
 )
 AS
 BEGIN
    
    SET NOCOUNT ON

    DECLARE @reportKey       INT
    DECLARE @packageKey      INT
    DECLARE @licenseNamesStr VARCHAR(MAX)
    DECLARE @licenseKeys     TABLE
    (
        [Key] INT NOT NULL PRIMARY KEY
    )

    -- Check input

    IF @sequence  IS NULL RETURN
    IF @packageId IS NULL RETURN
    IF @version   IS NULL RETURN

    DECLARE @returnTable TABLE
    (
        [Action] nvarchar(128),
        [Table] nvarchar(128),
        [Value] nvarchar(max)
    )
 
    -- Get package key
    
    SET @packageKey =
    (
        SELECT P.[Key]
        FROM   PackageRegistrations AS R
        JOIN   Packages AS P 
        ON     R.[Key] = P.PackageRegistrationKey
        WHERE  (R.Id = @packageId) AND (P.Version = @version)
    )

    IF @packageKey IS NULL
    BEGIN
        INSERT INTO @returnTable([Action], [Table], [Value]) VALUES('Error', 'Packages', 'Package not found: ' + @packageId + ' ' + @version)
        
        -- Return results
        SELECT -1 AS 'ReturnCode'
        SELECT * FROM @returnTable
    END
    ELSE
    BEGIN
    
        BEGIN TRANSACTION

            -- Add non-existing license names 
            MERGE PackageLicenses
            USING @licenseNames l
            ON    PackageLicenses.Name = l.Name
            WHEN NOT MATCHED THEN
                INSERT (Name) VALUES (l.Name)
            OUTPUT $action AS 'Action', 'PackageLicenses' AS 'Table', INSERTED.Name AS 'Value' 
                INTO @returnTable([Action], [Table], Value);
    
            -- Get license names keys
 
            INSERT @licenseKeys
            SELECT PackageLicenses.[Key] FROM PackageLicenses 
            JOIN   @licenseNames l
            ON     PackageLicenses.Name = l.Name

            -- Is there already a report for this ID Version Sequence tuple? If so, delete it
            DELETE FROM PackageLicenseReports
                OUTPUT 'DELETE' AS 'Action', 'PackageLicenseReports' AS 'Table', deleted.[Key] AS 'Value'
                    INTO @returnTable([Action], [Table], Value)
            WHERE PackageKey = @packageKey AND Sequence = @sequence

            -- Add report
            INSERT PackageLicenseReports (PackageKey, CreatedUtc, Sequence, ReportUrl, Comment)
                OUTPUT 'INSERT' AS 'Action', 'PackageLicenseReports' AS 'Table', CONVERT(nvarchar(64), INSERTED.[Key]) + ' URL:' + INSERTED.ReportUrl AS 'Value' 
                    INTO @returnTable([Action], [Table], Value)
            VALUES (@packageKey, GETDATE(), @sequence, @reportUrl, @Comment)

            SELECT @reportKey = SCOPE_IDENTITY()
 
            -- Create relationship between report and licenses names 
 
            -- INSERT PackageLicenseReportLicenses (ReportKey, LicenseKey)
            INSERT PackageLicenseReportLicenses
            SELECT @reportKey AS ReportKey, [Key] FROM @licenseKeys

            -- Add denormalized data for optimization 
    
            -- Creates a comma-separated list
            SELECT @licenseNamesStr = COALESCE(@licenseNamesStr + ',', '') + Name 
            FROM   @licenseNames
 
            DECLARE @pid TABLE (PackageKey INT NOT NULL PRIMARY KEY)
            UPDATE Packages
            SET    Packages.LicenseNames = @licenseNamesStr,
                    Packages.LicenseReportUrl = @reportUrl
            OUTPUT INSERTED.[Key]
                INTO @pid(PackageKey)
            FROM   Packages p
                INNER JOIN PackageRegistrations pr ON p.PackageRegistrationKey = pr.[Key]
            WHERE  p.[Key] = @packageKey

            INSERT INTO @returnTable([Action], [Table], [Value])
            SELECT 'UPDATE' AS 'Action', 'Packages' AS 'Table', pr.Id + ' ' + p.[Version] + ': Report=' + p.LicenseReportUrl + ' Names=' + p.LicenseNames
            FROM @pid pid
                INNER JOIN Packages p ON pid.PackageKey = p.[Key]
                INNER JOIN PackageRegistrations pr ON p.PackageRegistrationKey = pr.[Key]

            -- Return results
            SELECT 0 AS 'ReturnCode'
            SELECT * FROM @returnTable
       
        IF @whatIf <> 1 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION
    END
END